<?php

class Reservation_model extends CI_Model {

    function __construct() {
        parent::__construct();
    }

    function roomSearch($checkin, $checkout, $adult, $child, $infant) {
        $sql = "SELECT a.room_number, e.room_type_name, e.room_type_price FROM room a
            LEFT JOIN (select c.resv_room_id, b.room_number, c.resv_room_date from reservation b 
            join reservation_room c on b.resv_id = c.resv_id) d ON (a.room_number = d.room_number 
            AND d.resv_room_date BETWEEN '" . $checkin . "' AND '" . $checkout . "') 
            left join room_type e on a.room_type_id = e.room_type_id
                WHERE d.resv_room_id IS NULL and e.room_type_max_adult >= " . $adult . " 
                and e.room_type_max_child >= " . $child . " and e.room_type_max_infant >= " . $infant . "
                    ORDER BY a.room_number asc";

        $return = $this->db->query($sql);

        return $return->result_array();
    }

    function roomPrice($checkin, $checkout, $room_number) {
        $sql = "select a.room_number, b.room_type_name, b.room_type_price, c.rate_type_name, c.rate_type_id, d.room_rate_date, d.room_rate_price from room a 
left join room_type b on b.room_type_id = a.room_type_id
left join rate_type c on c.room_type_id = b.room_type_id
left join room_rate d on d.rate_type_id = c.rate_type_id
and d.room_rate_date between '" . $checkin . "' and '" . $checkout . "' where a.room_number = " . $room_number;

        $return = $this->db->query($sql);

        return $return->result_array();
    }

    function saveResv($staff) {
        $sql = "INSERT INTO `reservation` (`username`, `guest_id`, `resv_status`, `resv_booking_time`, 
            `resv_checkin`, `resv_checkout`, `resv_adult_number`, `resv_child_number`, `resv_infant_number`, `room_number`) 
            VALUES ('" . $staff . "', " . $_POST['guest_id'] . ", " . $_POST['status'] . ", NOW() " . "
                , '" . $_POST['checkin'] . "', '" . $_POST['checkout'] . "', " . $_POST['adult'] . " 
                    , " . $_POST['child'] . ", " . $_POST['infant'] . ", " . $_POST['room_number'][0] . ");";
        $this->db->query($sql);

        $sql = "select resv_id from reservation order by resv_id desc limit 1";

        $result = $this->db->query($sql);
        $lastResv = $result->result_array();

        if (empty($_POST['rate_type'][0])) {
            $_POST['rate_type'][0] = 0;
        }


        $sql2 = "INSERT INTO `reservation_room` (`resv_id`, `resv_room_date`, `rate_type_id`, `price`) 
            VALUES (" . $lastResv[0]['resv_id'] . ", '" . $_POST['tanggal'][0] . "', " . $_POST['rate_type'][0] . ", " . $_POST['price'][0] . ")";

        if ($_POST['tanggal'][1]) {
            for ($i = 1; $i < count($_POST['tanggal']); $i++) {
                if (empty($_POST['rate_type'][$i])) {
                    $_POST['rate_type'][$i] = 0;
                }

                $sql2 .= ", (" . $lastResv[0]['resv_id'] . ", '" . $_POST['tanggal'][$i] . "', " . $_POST['rate_type'][$i] . ", " . $_POST['price'][$i] . ") ";
            }
        }

        $this->db->query($sql2);
    }

    function getResv($resv_id) {
        $sql = "SELECT  resv_id, c.guest_name,  resv_status,  resv_checkin,  resv_checkout,  
            resv_adult_number,  resv_child_number,  resv_infant_number 
            FROM reservation a
            left join guest c on c.guest_id = a.guest_id
            where a.resv_id = " . $resv_id;
        $result = $this->db->query($sql);

        return $result->row_array();
    }

    function editResv() {
        $sql = "UPDATE `reservation` SET `resv_status` = " . $_POST['status'] . ", 
            `resv_checkout` = '" . $_POST['checkout'] . 
                "', `resv_adult_number` = " . $_POST['adult'] . ", `resv_child_number` = " . $_POST['child'] . "
                    , `resv_infant_number` = " . $_POST['infant'] . " WHERE `resv_id`=" . $_POST['resv_id'];
        $this->db->query($sql);
    }

    function editRoom($id) {
        $sql = "SELECT  a.resv_room_id,  b.room_number, e.room_type_name, a.resv_room_date,  c.rate_type_name,  price
FROM reservation_room a
join reservation b on b.resv_id = a.resv_id
join room d on d.room_number = b.room_number
join room_type e on e.room_type_id = d.room_number
left join rate_type c on c.rate_type_id = a.rate_type_id
where a.resv_id =" . $id;

        $sql = $this->db->query($sql);

        return $sql->result_array();
    }

    function listService($id) {
        $sql = "select resv_serv_id, resv_id,service_name, resv_serv_unit, resv_serv_total, resv_serv_desc
from reservation_service a 
join service b on b.service_id = a.service_id
where resv_id =" . $id;

        $sql = $this->db->query($sql);

        return $sql->result_array();
    }

    function listServ() {
        $sql = "SELECT  `service_id`,  `service_name`,  `service_price`,  `service_type_id` FROM `service`";

        $sql = $this->db->query($sql);

        return $sql->result_array();
    }

    function addServ() {
        $sql = "INSERT INTO reservation_service (`resv_id`, `service_id`, `resv_serv_unit`, `resv_serv_total`, `resv_serv_desc`) 
            VALUES (" . $_POST['resv_id'] . ", " . $_POST['service_id'] . ", " . $_POST['jumlah'] . ", " . $_POST['total'] . ", '" . $_POST['desc'] . "');";
        $this->db->query($sql);
    }
    
    function getServ($id) {
        $sql = "SELECT  resv_serv_id, `service_id`, `resv_serv_unit`, `resv_serv_total`, `resv_serv_desc` FROM `reservation_service` where resv_serv_id =".$id;

        $sql = $this->db->query($sql);

        return $sql->row_array();
    }
    
    function editServ() {
        $sql = "UPDATE `reservation_service` SET `service_id` = " . $_POST['service_id'] . ", `resv_serv_unit` = '" . $_POST['jumlah'] . "', `resv_serv_total` = " . $_POST['total'] . ", `resv_serv_desc` = '" . $_POST['desc'] . "' WHERE `resv_serv_id`=" . $_POST['resv_serv_id'];
        $this->db->query($sql);
    }

    function deleteServ($id) {
        $sql = "DELETE FROM `reservation_service` where resv_serv_id = " . $id;
        $this->db->query($sql);
    }

}